package com.ums.scumspay.bigdataimport.controller;

import com.ums.scumspay.bigdataimport.QryFile.*;
import com.ums.scumspay.bigdataimport.QryFile.ExceltoHtml.ExcelToHtml;
import com.ums.scumspay.bigdataimport.constant.FilePathConstant;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.annotation.PostConstruct;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.io.*;
import java.util.*;
import java.time.LocalDate;
import java.time.Month;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjuster;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Stream;

@Controller
public class FileUploadController {
    private static final Logger logger  = LoggerFactory.getLogger(FileUploadController.class);
    @PostMapping("/efficiency")
    public String uploadFile(@RequestParam("file") List<MultipartFile> file,@RequestParam("month") String month) throws IOException {
        if (StringUtils.isEmpty(month)){
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
            month=simpleDateFormat.format(new Date());
        }
        for (MultipartFile multipartFile : file) {
            // 获取原始文件名 绩效
            String originalFilename = multipartFile.getOriginalFilename();
            InputStream inputStream = multipartFile.getInputStream();
            if (originalFilename.contains("应收账款")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_ACCOUNTS_RECEIVABLE(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_ACCOUNTS_RECEIVABLE",month);
            } else if (originalFilename.contains("考勤")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_CHECKWORK(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_CHECKWORK",month);
            } else if (originalFilename.contains("合同回收")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_CONTRACT(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_CONTRACT",month);
            } else if (originalFilename.contains("客户工单派工情况")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_CUSTOMER_TASK(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_CUSTOMER_TASK",month);
            } else if (originalFilename.contains("营销联盟垫款明细")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MARKET_ADVANCES(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MARKET_ADVANCES",month);
            } else if (originalFilename.contains("营销联盟应收票据明细")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MARKET_INVOICE(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MARKET_INVOICE",month);
            } else if (originalFilename.contains("入网3个月确认风险")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE1(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE1",month);
            } else if (originalFilename.contains("风险调查核实")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE2(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE2",month);
            } else if (originalFilename.contains("未落商户实名制")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE3(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE3",month);
            }else if (originalFilename.contains("引发风险事件")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE4(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE4",month);
            }else if (originalFilename.contains("逾期未撤销")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE5(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE5",month);
            }else if (originalFilename.contains("其他纳入风险考核")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_MERCHANT_TABLE6(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_MERCHANT_TABLE6",month);
            }else if (originalFilename.contains("负收益")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_NEGATIVE_RETURN(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_NEGATIVE_RETURN",month);
            } else if (originalFilename.contains("OA代办文件")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcelForXls(inputStream, 1);
                MerXnService.EFF_OA_INFO(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_OA_INFO",month);
            } else if (originalFilename.contains("超期POS机具押金")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_POS_DEPOSIT(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_POS_DEPOSIT",month);
            } else if (originalFilename.contains("资金暂缓")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_RISK_FUND_SUSPENSION(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_RISK_FUND_SUSPENSION",month);
            } else if (originalFilename.contains("风险派工-工单")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_RISK_TASK(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_RISK_TASK",month);
            } else if (originalFilename.contains("风险派工-逾期")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_RISK_TASK_OVERDUE(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_RISK_TASK_OVERDUE",month);
            } else if (originalFilename.contains("风险派工-不合格")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_RISK_TASK_UNQUALIFIED(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_RISK_TASK_UNQUALIFIED",month);
            } else if (originalFilename.contains("银商学堂")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_UMS_SCHOOL(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_UMS_SCHOOL",month);
            } else if (originalFilename.contains("终端效能")) {
                HashMap<String, ArrayList<ArrayList<String>>> map = ExecUtil.readExcel(inputStream, 1);
                MerXnService.EFF_POS_EFFICIENCY(map.get("sheet0"), month);
                MerXnService.mergeInto("EFF_POS_EFFICIENCY",month);
            }
            inputStream.close();
        }

        return "efficiency";
    }


    @PostMapping("/upload/kpi")
    public String uploadFileByKPI(@RequestParam("file") List<MultipartFile> fileDirectory,@RequestParam("month") String month) throws IOException, ParseException {
        ExcelUtilEasyexcel.exportExcel(fileDirectory,month);
        return "efficiency";
    }

    @PostMapping("/upload/HHTrans")
    public String HHTrans(@RequestParam("file") MultipartFile file, @RequestParam("month") String month) throws IOException {
        File fileWrite = new File(FilePathConstant.filePath + "\\" + month + "\\");
        //文件路径不存在则创建
        if (!fileWrite.exists()) {
            fileWrite.mkdir();
            fileWrite.createNewFile();
        }
        FileOutputStream fileOutputStream = new FileOutputStream(fileWrite + "\\" + "HHTrans.sql");
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(file.getInputStream()));
        String line;
        //创建需要的数组与链表
        LinkedList<String> writeArrayList = new LinkedList<>();
        String[] _str;
        //链表增加数据较快
        while ((line=bufferedReader.readLine())!=null){
            writeArrayList.add(line);
        }
        //arraylist查询较快,将链表转换为数组
        ArrayList<String> arrayList = new ArrayList<>(writeArrayList);
        for (int i = 0; i < arrayList.size(); i++){
            _str = arrayList.get(i).split(",");
            String sql="INSERT INTO MERDATABASE.GRID_INFO(ID,SUBMCHNT_ID,PERSON_ID,PERSON_USERNAME,PERSON_TYPE,PERSON_SUB_TYPE) VALUES('"+_str[0]+"','"+_str[1]+"','"+_str[2]+"','"+_str[5]+"','"+_str[6]+"','"+_str[7]+"');";
            fileOutputStream.write((sql+ "\r\n").getBytes());
        }
        fileOutputStream.write("COMMIT;".getBytes());
        bufferedReader.close();
        fileOutputStream.close();
        return "efficiency";
    }

    @PostMapping("/efficiency/delete")
    public String deleteFile(HttpServletRequest request) throws IOException {
        String month = request.getParameter("month");
        String property = System.getProperty("user.dir");
        File file = new File(property + "\\efficiency-"+month+".sql");
        File file1 = new File(property + "\\efficiency-mergeInto-"+month+".sql");
        file.delete();
        file1.delete();
        return "efficiency";
    }

    @PostMapping("/prod")
    public String prodFile(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws IOException {
        String sheet=request.getParameter("sheet");
        InputStream inputStream = file.getInputStream();
        HashMap<String, ArrayList<ArrayList<String>>> map=ExecUtil.readExcel(inputStream,1);
        File newfile = new File(FilePathConstant.filePath+"\\prod_"+new SimpleDateFormat("yyyyMMdd").format(new Date())+".sql");
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newfile), "UTF-8"));
        switch (sheet){
            case "sheet0":
                Achievements.sheetSR(map.get("sheet0"),out);
                break;
            case "sheet1":
                Achievements.sheetcb(map.get("sheet1"),out);
                break;
            case "sheet2":
                Achievements.sheetfy(map.get("sheet2"),out);
                break;
            case "sheet3":
                Achievements.sheetqncpjj(map.get("sheet3"),out);
                break;
            case "sheet4":
                Achievements.sheetqntq(map.get("sheet0"),out);
                break;
        }
        inputStream.close();
        out.close();
        return "efficiency";
    }

    @PostMapping("/prod/delete")
    public String deleteProdFile() throws IOException {
        File newfile = new File(FilePathConstant.filePath+"\\prod_"+new SimpleDateFormat("yyyyMMdd").format(new Date())+".sql");
        newfile.delete();
        return "efficiency";
    }


    @PostMapping("/crtHtml")
    public String createHtml(HttpServletRequest request) throws IOException {
        String year = request.getParameter("year");
        String month = request.getParameter("month");
        String path=FilePathConstant.proFilePath+"\\"+month+"月\\";
        String outPath=FilePathConstant.proFilePath+"\\"+month+"月\\"+"html\\";
        // String outPath="F:\\bigdata\\temp\\部门财务指标\\4月\\";
        String prefixYear=year+"年"+month+"月";
        String prefixMonth="";
        if (Integer.parseInt(month)>=1 && Integer.parseInt(month)<10){
            prefixMonth=year+"0"+month;
        }else {
            prefixMonth=year+month;
        }
        List<String> list=new ArrayList<String>();
        list.add(prefixYear+"_达州.xlsx,"+prefixMonth+"_10031.html");
        list.add(prefixYear+"_大客户服务室.xlsx,"+prefixMonth+"_大客户服务室.html");
        list.add(prefixYear+"_德阳.xlsx,"+prefixMonth+"_10025.html");
        // list.add("2023年4月_风险合规部.xlsx,202304_10003.html");
        list.add(prefixYear+"_甘孜.xlsx,"+prefixMonth+"_10041.html");
        list.add(prefixYear+"_广安.xlsx,"+prefixMonth+"_10039.html");
        list.add(prefixYear+"_广元.xlsx,"+prefixMonth+"_10027.html");
        // list.add("2023年4月_技术部.xlsx,202304_10005.html");
        list.add(prefixYear+"_郊县二部.xlsx,"+prefixMonth+"_10016.html");
        list.add(prefixYear+"_郊县一部.xlsx,"+prefixMonth+"_10015.html");
        list.add(prefixYear+"_金融增值产品室.xlsx,"+prefixMonth+"_金融增值产品室.html");
        list.add(prefixYear+"_乐山.xlsx,"+prefixMonth+"_10024.html");
        list.add(prefixYear+"_凉山.xlsx,"+prefixMonth+"_10034.html");
        list.add(prefixYear+"_泸州.xlsx,"+prefixMonth+"_10022.html");
        list.add(prefixYear+"_眉山.xlsx,"+prefixMonth+"_10035.html");
        list.add(prefixYear+"_绵阳.xlsx,"+prefixMonth+"_10032.html");
        list.add(prefixYear+"_南充.xlsx,"+prefixMonth+"_10023.html");
        list.add(prefixYear+"_内江.xlsx,"+prefixMonth+"_10028.html");
        list.add(prefixYear+"_攀枝花.xlsx,"+prefixMonth+"_10030.html");
        list.add(prefixYear+"_商户服务部.xlsx,"+prefixMonth+"_10008.html");
        list.add(prefixYear+"_遂宁.xlsx,"+prefixMonth+"_10029.html");
        list.add(prefixYear+"_拓展二部.xlsx,"+prefixMonth+"_10014.html");
        list.add(prefixYear+"_拓展一部.xlsx,"+prefixMonth+"_10013.html");
        list.add(prefixYear+"_文旅大数据拓展团队.xlsx,"+prefixMonth+"_文旅大数据拓展团队.html");
        list.add(prefixYear+"_行业拓展室.xlsx,"+prefixMonth+"_10012.html");
        list.add(prefixYear+"_雅安.xlsx,"+prefixMonth+"_10037.html");
        // list.add("2023年4月_业务运营部.xlsx,202304_10004.html");
        list.add(prefixYear+"_宜宾.xlsx,"+prefixMonth+"_10026.html");
        list.add(prefixYear+"_营销服务部.xlsx,"+prefixMonth+"_10017.html");
        list.add(prefixYear+"_支付业务室.xlsx,"+prefixMonth+"_支付业务室.html");
        list.add(prefixYear+"_资阳.xlsx,"+prefixMonth+"_10038.html");
        list.add(prefixYear+"_自贡.xlsx,"+prefixMonth+"_10033.html");
        list.add(prefixYear+"_自助渠道业务部.xlsx,"+prefixMonth+"_10006.html");
        list.add(prefixYear+"_阿坝.xlsx,"+prefixMonth+"_10040.html");
        list.add(prefixYear+"_巴中.xlsx,"+prefixMonth+"_10036.html");

        for (int i = 0; i < list.size(); i++) {
            String[] _str=list.get(i).split(",");
            InputStream is = null;
            String htmlExcel = null;
            Writer or = null;
            BufferedWriter bor = null;
            try {
                File sourcefile = new File(path+_str[0]);
                File source = new File(outPath+_str[1]);
                is = new FileInputStream(sourcefile);
                or = new FileWriter(source);
                bor = new BufferedWriter(or);
                Workbook wb = WorkbookFactory.create(is);// 此WorkbookFactory在POI-3.10版本中使用需要添加dom4j
                if (wb instanceof XSSFWorkbook) {
                    XSSFWorkbook xWb = (XSSFWorkbook) wb;
                    htmlExcel = ExcelToHtml.getExcelInfo(xWb, true);
                } else if (wb instanceof HSSFWorkbook) {
                    HSSFWorkbook hWb = (HSSFWorkbook) wb;
                    htmlExcel = ExcelToHtml.getExcelInfo(hWb, true);
                }
                bor.write(htmlExcel);
                bor.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    is.close();
                } catch (IOException e) {
                    logger.debug("ExcelToHtml IOException:"+e);
                }
                try {
                    bor.close();
                } catch (IOException e) {
                    logger.debug("ExcelToHtml IOException:"+e);
                }
                try {
                    or.close();
                } catch (IOException e) {
                    logger.debug("ExcelToHtml IOException:"+e);
                }
            }
        }

        return "efficiency";
    }
}